import { RowDataPacket, ResultSetHeader } from 'mysql2';
import { pool } from '../utils/database';
import { Order, OrderDetail, OrderQueryParams, CreateOrderRequest, UpdateOrderRequest, OrderStats } from '../types/order';

export class OrderModel {
  // 创建订单
  static async create(orderData: CreateOrderRequest & { orderNo: string; totalAmount: number; goodsAmount: number }): Promise<number> {
    const connection = await pool.getConnection();
    
    try {
      await connection.beginTransaction();
      
      // 插入订单主表
      const [orderResult] = await connection.execute<ResultSetHeader>(
        `INSERT INTO orders (
          order_no, user_id, order_status, payment_status, shipping_status,
          consignee_name, consignee_phone, consignee_address,
          total_amount, goods_amount, shipping_fee, points_amount,
          buyer_message, auto_confirm_day
        ) VALUES (?, ?, 1, 0, 0, ?, ?, ?, ?, ?, ?, ?, ?, 7)`,
        [
          orderData.orderNo,
          orderData.userId,
          orderData.consigneeName,
          orderData.consigneePhone,
          orderData.consigneeAddress,
          orderData.totalAmount,
          orderData.goodsAmount,
          orderData.shippingFee || 0,
          orderData.pointsAmount || 0,
          orderData.buyerMessage || null
        ]
      );
      
      const orderId = orderResult.insertId;
      
      // 插入订单商品项
      for (const item of orderData.items) {
        // 获取商品信息
        const [productRows] = await connection.execute<RowDataPacket[]>(
          'SELECT name, main_image FROM products WHERE id = ?',
          [item.productId]
        );
        
        let skuSpecifications = null;
        if (item.skuId) {
          const [skuRows] = await connection.execute<RowDataPacket[]>(
            'SELECT specifications FROM product_skus WHERE id = ?',
            [item.skuId]
          );
          if (skuRows.length > 0) {
            skuSpecifications = skuRows[0].specifications;
          }
        }
        
        const product = productRows[0];
        const itemTotal = item.price * item.quantity;
        
        await connection.execute(
          `INSERT INTO order_items (
            order_id, product_id, sku_id, product_name, product_image,
            sku_specifications, price, quantity, total_amount
          ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            orderId,
            item.productId,
            item.skuId || null,
            product.name,
            product.main_image || null,
            skuSpecifications,
            item.price,
            item.quantity,
            itemTotal
          ]
        );
      }
      
      await connection.commit();
      return orderId;
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }
  
  // 根据ID获取订单详情
  static async findById(id: number): Promise<OrderDetail | null> {
    const [orderRows] = await pool.execute<RowDataPacket[]>(
      `SELECT o.*, u.username, u.phone as user_phone, u.email 
       FROM orders o 
       LEFT JOIN users u ON o.user_id = u.id 
       WHERE o.id = ?`,
      [id]
    );
    
    if (orderRows.length === 0) {
      return null;
    }
    
    const order = orderRows[0];
    
    // 获取订单商品项
    const [itemRows] = await pool.execute<RowDataPacket[]>(
      'SELECT * FROM order_items WHERE order_id = ? ORDER BY id',
      [id]
    );
    
    return {
      id: order.id,
      orderNo: order.order_no,
      userId: order.user_id,
      orderStatus: order.order_status,
      paymentStatus: order.payment_status,
      shippingStatus: order.shipping_status,
      consigneeName: order.consignee_name,
      consigneePhone: order.consignee_phone,
      consigneeAddress: order.consignee_address,
      totalAmount: parseFloat(order.total_amount),
      goodsAmount: parseFloat(order.goods_amount),
      shippingFee: parseFloat(order.shipping_fee),
      pointsAmount: parseFloat(order.points_amount),
      paymentMethod: order.payment_method,
      paymentTime: order.payment_time,
      shippingTime: order.shipping_time,
      confirmTime: order.confirm_time,
      deliveryCompany: order.delivery_company,
      deliveryNo: order.delivery_no,
      buyerMessage: order.buyer_message,
      sellerRemark: order.seller_remark,
      autoConfirmDay: order.auto_confirm_day,
      createdAt: order.created_at,
      updatedAt: order.updated_at,
      items: itemRows.map((item: any) => ({
        id: item.id,
        orderId: item.order_id,
        productId: item.product_id,
        skuId: item.sku_id,
        productName: item.product_name,
        productImage: item.product_image,
        skuSpecifications: item.sku_specifications,
        price: parseFloat(item.price),
        quantity: item.quantity,
        totalAmount: parseFloat(item.total_amount),
        createdAt: item.created_at
      })),
      user: {
        id: order.user_id,
        username: order.username,
        phone: order.user_phone,
        email: order.email
      }
    };
  }
  
  // 根据订单号获取订单
  static async findByOrderNo(orderNo: string): Promise<OrderDetail | null> {
    const [orderRows] = await pool.execute<RowDataPacket[]>(
      'SELECT id FROM orders WHERE order_no = ?',
      [orderNo]
    );
    
    if (orderRows.length === 0) {
      return null;
    }
    
    return this.findById(orderRows[0].id);
  }
  
  // 分页查询订单列表
  static async findAll(params: OrderQueryParams): Promise<{ orders: Order[], total: number }> {
    console.log('findAll params:', JSON.stringify(params, null, 2));
    
    const {
      page = 1,
      limit = 20,
      orderNo,
      userId,
      orderStatus,
      paymentStatus,
      shippingStatus,
      startDate,
      endDate,
      consigneeName,
      consigneePhone,
      sort = 'created_at',
      order = 'DESC'
    } = params;

    // 安全的排序字段白名单
    const allowedSortFields = ['id', 'order_no', 'created_at', 'updated_at', 'total_amount', 'order_status'];
    const safeSort = allowedSortFields.includes(sort) ? sort : 'created_at';
    const safeOrder = order === 'ASC' ? 'ASC' : 'DESC';
    
    // 构建查询条件
    let whereClause = 'WHERE 1=1';
    const queryParams: any[] = [];
    
    if (orderNo) {
      whereClause += ' AND order_no LIKE ?';
      queryParams.push(`%${orderNo}%`);
    }
    
    if (userId) {
      whereClause += ' AND user_id = ?';
      queryParams.push(userId);
    }
    
    if (orderStatus !== undefined) {
      whereClause += ' AND order_status = ?';
      queryParams.push(orderStatus);
    }
    
    if (paymentStatus !== undefined) {
      whereClause += ' AND payment_status = ?';
      queryParams.push(paymentStatus);
    }
    
    if (shippingStatus !== undefined) {
      whereClause += ' AND shipping_status = ?';
      queryParams.push(shippingStatus);
    }
    
    if (startDate) {
      whereClause += ' AND DATE(created_at) >= ?';
      queryParams.push(startDate);
    }
    
    if (endDate) {
      whereClause += ' AND DATE(created_at) <= ?';
      queryParams.push(endDate);
    }
    
    if (consigneeName) {
      whereClause += ' AND consignee_name LIKE ?';
      queryParams.push(`%${consigneeName}%`);
    }
    
    if (consigneePhone) {
      whereClause += ' AND consignee_phone LIKE ?';
      queryParams.push(`%${consigneePhone}%`);
    }

    try {
      // 获取总数
      const [countRows] = await pool.execute<RowDataPacket[]>(
        `SELECT COUNT(*) as total FROM orders ${whereClause}`,
        queryParams
      );
      const total = countRows[0].total;
      
      // 获取订单列表
      const offset = (page - 1) * limit;
      // 确保limit和offset是数字类型
      const numLimit = Number(limit);
      const numOffset = Number(offset);
      
      const [orderRows] = await pool.execute<RowDataPacket[]>(
        `SELECT * FROM orders 
         ${whereClause} 
         ORDER BY ${safeSort} ${safeOrder} 
         LIMIT ? OFFSET ?`,
        [...queryParams, numLimit, numOffset]
      );
    
      const orders: Order[] = orderRows.map((row: any) => ({
        id: row.id,
        orderNo: row.order_no,
        userId: row.user_id,
        orderStatus: row.order_status,
        paymentStatus: row.payment_status,
        shippingStatus: row.shipping_status,
        consigneeName: row.consignee_name,
        consigneePhone: row.consignee_phone,
        consigneeAddress: row.consignee_address,
        totalAmount: parseFloat(row.total_amount),
        goodsAmount: parseFloat(row.goods_amount),
        shippingFee: parseFloat(row.shipping_fee),
        pointsAmount: parseFloat(row.points_amount),
        paymentMethod: row.payment_method,
        paymentTime: row.payment_time,
        shippingTime: row.shipping_time,
        confirmTime: row.confirm_time,
        deliveryCompany: row.delivery_company,
        deliveryNo: row.delivery_no,
        buyerMessage: row.buyer_message,
        sellerRemark: row.seller_remark,
        autoConfirmDay: row.auto_confirm_day,
        createdAt: row.created_at,
        updatedAt: row.updated_at
      }));
      
      return { orders, total };
    } catch (error) {
      console.error('Order findAll error:', error);
      throw error;
    }
  }
  
  // 更新订单
  static async update(id: number, updateData: UpdateOrderRequest): Promise<boolean> {
    const fields: string[] = [];
    const values: any[] = [];
    
    if (updateData.orderStatus !== undefined) {
      fields.push('order_status = ?');
      values.push(updateData.orderStatus);
    }
    
    if (updateData.paymentStatus !== undefined) {
      fields.push('payment_status = ?');
      values.push(updateData.paymentStatus);
      
      // 如果设置为已支付，记录支付时间
      if (updateData.paymentStatus === 1) {
        fields.push('payment_time = NOW()');
      }
    }
    
    if (updateData.shippingStatus !== undefined) {
      fields.push('shipping_status = ?');
      values.push(updateData.shippingStatus);
      
      // 如果设置为已发货，记录发货时间
      if (updateData.shippingStatus === 1) {
        fields.push('shipping_time = NOW()');
      }
      // 如果设置为已收货，记录确认收货时间
      else if (updateData.shippingStatus === 2) {
        fields.push('confirm_time = NOW()');
      }
    }
    
    if (updateData.paymentMethod !== undefined) {
      fields.push('payment_method = ?');
      values.push(updateData.paymentMethod);
    }
    
    if (updateData.deliveryCompany !== undefined) {
      fields.push('delivery_company = ?');
      values.push(updateData.deliveryCompany);
    }
    
    if (updateData.deliveryNo !== undefined) {
      fields.push('delivery_no = ?');
      values.push(updateData.deliveryNo);
    }
    
    if (updateData.sellerRemark !== undefined) {
      fields.push('seller_remark = ?');
      values.push(updateData.sellerRemark);
    }
    
    if (fields.length === 0) {
      return false;
    }
    
    fields.push('updated_at = NOW()');
    values.push(id);
    
    const [result] = await pool.execute<ResultSetHeader>(
      `UPDATE orders SET ${fields.join(', ')} WHERE id = ?`,
      values
    );
    
    return result.affectedRows > 0;
  }
  
  // 删除订单
  static async delete(id: number): Promise<boolean> {
    const [result] = await pool.execute<ResultSetHeader>(
      'DELETE FROM orders WHERE id = ?',
      [id]
    );
    
    return result.affectedRows > 0;
  }
  
  // 获取订单统计
  static async getStats(): Promise<OrderStats> {
    const [statsRows] = await pool.execute<RowDataPacket[]>(`
      SELECT 
        COUNT(*) as total_orders,
        SUM(CASE WHEN order_status = 1 THEN 1 ELSE 0 END) as pending_payment,
        SUM(CASE WHEN order_status = 2 THEN 1 ELSE 0 END) as pending_shipment,
        SUM(CASE WHEN order_status = 3 THEN 1 ELSE 0 END) as pending_receipt,
        SUM(CASE WHEN order_status = 4 THEN 1 ELSE 0 END) as completed,
        SUM(CASE WHEN order_status = 5 THEN 1 ELSE 0 END) as cancelled,
        SUM(CASE WHEN order_status = 6 THEN 1 ELSE 0 END) as refunding,
        SUM(total_amount) as total_amount
      FROM orders
    `);
    
    const [todayRows] = await pool.execute<RowDataPacket[]>(`
      SELECT 
        COUNT(*) as today_orders,
        COALESCE(SUM(total_amount), 0) as today_amount
      FROM orders 
      WHERE DATE(created_at) = CURDATE()
    `);
    
    const stats = statsRows[0];
    const today = todayRows[0];
    
    return {
      totalOrders: stats.total_orders,
      pendingPayment: stats.pending_payment,
      pendingShipment: stats.pending_shipment,
      pendingReceipt: stats.pending_receipt,
      completed: stats.completed,
      cancelled: stats.cancelled,
      refunding: stats.refunding,
      totalAmount: parseFloat(stats.total_amount) || 0,
      todayOrders: today.today_orders,
      todayAmount: parseFloat(today.today_amount) || 0
    };
  }
  
  // 生成订单号
  static generateOrderNo(): string {
    const now = new Date();
    const timestamp = now.getTime();
    const random = Math.floor(Math.random() * 1000).toString().padStart(3, '0');
    return `${timestamp}${random}`;
  }
}